from openpyxl import load_workbook
import openpyxl
from datetime import datetime
import win32com.client
from QuickTools.tools_pmc import (lj_info, lj_qgmxb_y, lj_kcmxz
                                  )

excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False
excel.DisplayAlerts = False

e_wb = excel.Workbooks.Open(lj_kcmxz)
e_ws = e_wb.Worksheets(1)
e_ws.Sort.SortFields.Add(Key=e_ws.Range("A2"), Order=win32com.client.constants.xlAscending)  # 添加排序条件，按顺序添加
e_ws.Sort.SetRange(e_ws.Range("A2:S" + str(e_ws.UsedRange.Rows.Count)))  # 这里需要注意，设置需要排序的范围。
e_ws.Sort.Apply()  # 执行排序
e_wb.SaveAs(lj_kcmxz)
e_wb.Close()

e_wb = excel.Workbooks.Open(lj_qgmxb_y)
e_ws = e_wb.Worksheets(1)
e_ws.Sort.SortFields.Add(Key=e_ws.Range("J2"), Order=win32com.client.constants.xlAscending)  # 添加排序条件，按顺序添加
e_ws.Sort.SetRange(e_ws.Range("A2:AB" + str(e_ws.UsedRange.Rows.Count)))  # 这里需要注意，设置需要排序的范围。
e_ws.Sort.Apply()  # 执行排序
e_wb.SaveAs(lj_qgmxb_y)
e_wb.Close()

wb_qgmxb = load_workbook(lj_qgmxb_y)
ws_qgmxb = wb_qgmxb.worksheets[0]

wb_info = openpyxl.Workbook()
ws = wb_info.create_sheet('最后请购', 0)
a = 2
for i in range(3, ws_qgmxb.max_row + 1):
    print(i)
    if ws_qgmxb.cell(i, 15).value is not None and ws_qgmxb.cell(i, 15).value != "":
        if ws.cell(a - 1, 1).value != ws_qgmxb.cell(i, 10).value:
            ws.cell(a, 1).value = ws_qgmxb.cell(i, 10).value
            ws.cell(a, 2).value = datetime.strptime(ws_qgmxb.cell(i, 1).value, "%Y-%m-%d").strftime("%Y/%m/%d")
            ws.cell(a, 3).value = ws_qgmxb.cell(i, 14).value
            ws.cell(a, 4).value = ws_qgmxb.cell(i, 28).value
            ws.cell(a, 5).value = ws_qgmxb.cell(i, 21).value + '-' + ws_qgmxb.cell(i, 23).value
            a += 1
        else:
            if datetime.strptime(ws_qgmxb.cell(i, 15).value, "%Y-%m-%d") > datetime.strptime(ws.cell(a - 1, 2).value,
                                                                                             "%Y/%m/%d"):
                ws.cell(a - 1, 2).value = datetime.strptime(ws_qgmxb.cell(i, 1).value, "%Y-%m-%d").strftime("%Y/%m/%d")
                ws.cell(a - 1, 3).value = ws_qgmxb.cell(i, 14).value
                ws.cell(a - 1, 4).value = ws_qgmxb.cell(i, 28).value
                ws.cell(a - 1, 5).value = ws_qgmxb.cell(i, 21).value + '-' + ws_qgmxb.cell(i, 23).value

wb_kcmxz = load_workbook(lj_kcmxz)
ws_kcmxz = wb_kcmxz.active
ws = wb_info.create_sheet('最后工单出库', 0)
a = 2
for i in range(2, ws_kcmxz.max_row + 1):
    print(i)
    if ws_kcmxz.cell(i, 15).value == "厂内领料单":
        if ws.cell(a - 1, 1).value != ws_kcmxz.cell(i, 1).value:
            ws.cell(a, 1).value = ws_kcmxz.cell(i, 1).value
            ws.cell(a, 2).value = datetime.strptime(ws_kcmxz.cell(i, 5).value, "%Y-%m-%d").strftime("%Y/%m/%d")
            ws.cell(a, 3).value = ws_kcmxz.cell(i, 8).value
            ws.cell(a, 4).value = ws_kcmxz.cell(i, 18).value
            a += 1
        else:
            if datetime.strptime(ws_kcmxz.cell(i, 5).value, "%Y-%m-%d") > datetime.strptime(ws.cell(a - 1, 2).value,
                                                                                            "%Y/%m/%d"):
                ws.cell(a - 1, 2).value = datetime.strptime(ws_kcmxz.cell(i, 5).value, "%Y-%m-%d").strftime("%Y/%m/%d")
                ws.cell(a - 1, 3).value = ws_kcmxz.cell(i, 8).value
                ws.cell(a - 1, 4).value = ws_kcmxz.cell(i, 18).value
wb_info.save(lj_info)
